/** FASSIT (Forms of Selective Attention in Intelligent Transportation Systems)
 * Computational Creativity and Digital Media
 * Cognitive and Media Systems Group
 * Centre for Informatics and Systems of the University of Coimbra (CISUC)
 *
 * Copyright (c) 2010-2013 University of Coimbra, Portugal
 * All rights reserved.
 */

package masterAgent.DBmanager;

import agent.Context.Goal;
import poi.Info.*;
import functionsVariables.Constants;
import functionsVariables.FunctionsANDvariables;
import functionsVariables.MySQLConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

import com.mysql.jdbc.Connection;
import com.ginsberg.gowalla.dto.GeoPoint;

/**
 * In this class defines all the low level methods to communicate with the mysql
 * database
 * 
 * @author Hernani Costa
 * @version 0.1 date: 20/11/2012
 */
public class DBConnection {
	private static final long serialVersionUID = 1L;

	private FunctionsANDvariables fv = null;
	private MySQLConnection dbconnection = null;
	private Connection connection = null;

	/**
	 * ------------------------------------------------------------------------
	 * Constructor class
	 * ------------------------------------------------------------------------
	 */
	public DBConnection() {
		// openConnection();
		fv = new FunctionsANDvariables();
		dbconnection = MySQLConnection.getInstance();
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns all the POIs within a radius, for a specific Goal
	 * ------------------------------------------------------------------------
	 * 
	 * @param userLocation
	 *            - current user location
	 * @param radius
	 *            - radius to search POIs
	 * @param goal
	 *            - Goal
	 * @return an arrayList with all the POIs within the radius
	 */
	public HashMap<Integer, POI> getPOIsNearLocation(Location userLocation,
			int radius, String goal) {
		Statement stt;
		ResultSet rs;
		HashMap<Integer, POI> poisList = new HashMap<Integer, POI>();
		double lat = 0.0, lng = 0.0;
		Location location = null;
		POI poi = null;
		QueryGoal queryGoal = new QueryGoal();

		try {
			connection = dbconnection.openConnection();
			stt = connection.createStatement();
			rs = stt.executeQuery("SELECT x.id, x.catID, y.catName, x.subCatID, w.subCatName, x.name, x.lat, x.lng, x.numCheckins, x.price, x.schedule, x.daysOff FROM ex3_masterknowledge x, ex3_categories y, ex3_subcategories w WHERE x.price != '' AND x.catID=y.catID AND x.subCatID=w.subCatID AND ( "
					+ queryGoal.getQuery(goal) + ")");

			while (rs.next()) {
				lat = rs.getDouble(7);
				lng = rs.getDouble(8);
				location = new Location(lat, lng);

				if (location.getGeoPoint().getDistanceMeters(
						userLocation.getGeoPoint()) <= radius) {
					poi = createPOI(rs.getInt(1), rs.getInt(2), rs.getString(3)
							.trim(), rs.getInt(4), rs.getString(5).trim(), rs
							.getString(6).trim(), rs.getDouble(7),
							rs.getDouble(8), rs.getInt(9), rs.getString(10)
									.trim(), rs.getString(11).trim(), rs
									.getString(12).trim());
					poisList.put(poi.getPoiID(), poi);
				}
			}
			System.err.println("Numero de POIs: " + poisList.size());
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
		return poisList;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns a specific POI
	 * ------------------------------------------------------------------------
	 * 
	 * @param poiID
	 * @return the specific POI
	 */
	public POI getPOI(int poiID) {
		POI poi = null;
		try {
			connection = dbconnection.openConnection();
			Statement st = connection.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT x.id, x.catID, y.catName, x.subCatID, w.subCatName, x.name, x.lat, x.lng, x.numCheckins, x.price, x.schedule, x.daysOff FROM ex3_masterknowledge x, ex3_categories y, ex3_subcategories w WHERE x.catID=y.catID AND x.subCatID=w.subCatID AND x.id = '"
							+ poiID + "'");
			while (rs.next()) {
				poi = createPOI(rs.getInt(1), rs.getInt(2), rs.getString(3)
						.trim(), rs.getInt(4), rs.getString(5).trim(), rs
						.getString(6).trim(), rs.getDouble(7), rs.getDouble(8),
						rs.getInt(9), rs.getString(10).trim(), rs.getString(11)
								.trim(), rs.getString(12).trim());
				dbconnection.closeConnection();
				return poi;
			}
		} catch (SQLException e) {
			dbconnection.closeConnection();
			e.printStackTrace();
		}
		dbconnection.closeConnection();
		return null;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Creates the object POI
	 * ------------------------------------------------------------------------
	 * 
	 * @param poiID
	 * @param catID
	 * @param catName
	 * @param subCatID
	 * @param subCatName
	 * @param poiName
	 * @param poiLat
	 * @param poiLng
	 * @param numCheckins
	 * @param poiPrice
	 * @param poiSchedule
	 * @param poiDaysOff
	 * @return the object POI
	 */
	private POI createPOI(int poiID, int catID, String catName, int subCatID,
			String subCatName, String poiName, double poiLat, double poiLng,
			int numCheckins, String poiPrice, String poiSchedule,
			String poiDaysOff) {
		Location location = new Location(poiLat, poiLng);
		SubCategory subCategory = new SubCategory(subCatName, subCatID);
		Category category = new Category(catName, catID);

		// PRICE
		Price price = new Price(poiPrice);

		Schedule schedule = new Schedule(poiSchedule);

		// DaysOFF
		DaysOff daysOff = new DaysOff();
		if (!poiDaysOff.equals("") && !poiDaysOff.equals("Never")) {
			String[] dayOffTemp = poiDaysOff.split(",");
			for (String temp : dayOffTemp) {
				daysOff.addDayOff(temp);
			}
		}

		// DaysON - receives the daysOff and the schedule for the open days
		DaysOn daysOn = new DaysOn(daysOff, schedule);

		// new POI
		POI poi = new POI(poiName, poiID, numCheckins, price, location,
				category, subCategory, daysOn, daysOff);

		return poi;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns an array with all the subCategories names available for a given
	 * goal
	 * ------------------------------------------------------------------------
	 * 
	 * no codigo da BB corresponde a --> getCategoryByGoal
	 * 
	 * @param goal
	 * @return an arrayList with all the subCategories
	 */
	public ArrayList<String> getSubCategoriesNameByGoal(String goal) {
		ArrayList<String> categories = new ArrayList<String>();
		String query = "";
		QueryGoal queryGoal = new QueryGoal();
		Goal goals = new Goal();

		if (goal.equals(goals.getCoffee())) {
			query = "SELECT w.subCatName FROM ex3_subcategories w WHERE "
					+ queryGoal.getQueryCoffee();
		} else if (goal.equals(goals.getDinner())
				|| goal.equals(goals.getLunch()))
			query = "SELECT w.subCatName FROM ex3_subcategories w WHERE  "
					+ queryGoal.getQueryLunch();
		else if (goal.equals(goals.getParty()))
			query = "SELECT w.subCatName FROM ex3_subcategories w WHERE  "
					+ queryGoal.getQueryParty();
		else if (goal.equals(goals.getShopping()))
			query = "SELECT w.subCatName FROM ex3_subcategories w WHERE  "
					+ queryGoal.getQueryShopping();

		try {
			connection = dbconnection.openConnection();
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				categories.add(rs.getString(1));
			st.close();
			rs.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			dbconnection.closeConnection();
			e.printStackTrace();
		}
		return categories;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns an array with all the subCategories IDs available for a given
	 * goal
	 * ------------------------------------------------------------------------
	 * 
	 * @param goal
	 * @return an arrayList with all the subCategories IDs
	 */
	private ArrayList<Integer> getSubCategoriesIDByGoal(String goal) {
		ArrayList<Integer> categories = new ArrayList<Integer>();
		String query = "";

		QueryGoal queryGoal = new QueryGoal();
		Goal goals = new Goal();

		if (goal.equals(goals.getCoffee())) {
			query = "SELECT w.subCatID FROM ex3_subcategories w WHERE "
					+ queryGoal.getQueryCoffee();
		} else if (goal.equals(goals.getDinner())
				|| goal.equals(goals.getLunch()))
			query = "SELECT w.subCatID FROM ex3_subcategories w WHERE  "
					+ queryGoal.getQueryLunch();
		else if (goal.equals(goals.getParty()))
			query = "SELECT w.subCatID FROM ex3_subcategories w WHERE  "
					+ queryGoal.getQueryParty();
		else if (goal.equals(goals.getShopping()))
			query = "SELECT w.subCatID FROM ex3_subcategories w WHERE  "
					+ queryGoal.getQueryShopping();

		try {
			connection = dbconnection.openConnection();
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next())
				categories.add(rs.getInt(1));
			st.close();
			rs.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			dbconnection.closeConnection();
			e.printStackTrace();
		}
		return categories;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns an array with all the POIs for a specific subCategory ID
	 * ------------------------------------------------------------------------
	 * 
	 * @param subCatID
	 *            - subCategoryID
	 * @return an arrayList with all the POIs for a specific subCategory ID
	 */
	public ArrayList<POI> getPOIsBySubCatID(int subCatID) {
		ArrayList<POI> pois = new ArrayList<POI>();
		POI poi = null;
		try {
			connection = dbconnection.openConnection();
			Statement st = connection.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT x.id, x.catID, y.catName, x.subCatID, w.subCatName, x.name, x.lat, x.lng, x.numCheckins, x.price, x.schedule, x.daysOff FROM ex3_masterknowledge x, ex3_categories y, ex3_subcategories w WHERE x.catID=y.catID AND x.subCatID=w.subCatID AND x.subCatID = '"
							+ subCatID + "'");
			while (rs.next()) {
				poi = createPOI(rs.getInt(1), rs.getInt(2), rs.getString(3)
						.trim(), rs.getInt(4), rs.getString(5).trim(), rs
						.getString(6).trim(), rs.getDouble(7), rs.getDouble(8),
						rs.getInt(9), rs.getString(10).trim(), rs.getString(11)
								.trim(), rs.getString(12).trim());
				pois.add(poi);
			}
			dbconnection.closeConnection();
			return pois;
		} catch (SQLException e) {
			dbconnection.closeConnection();
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns the name of the subCategory, given its ID
	 * ------------------------------------------------------------------------
	 * 
	 * @param subCatID
	 * @return name of the subCategory
	 */
	private String getSubCatName(int subCatID) {
		try {
			String subcatName = "";
			connection = dbconnection.openConnection();
			Statement st = connection.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT subCatName FROM ex3_subcategories WHERE subcatID = '"
							+ subCatID + "'");
			while (rs.next())
				subcatName = rs.getString(1);
			st.close();
			rs.close();
			dbconnection.closeConnection();
			return subcatName;
		} catch (SQLException e) {
			dbconnection.closeConnection();
			e.printStackTrace();
			return Constants.getErrorStr();
		}
	}// END getSubCatName

	/**
	 * ------------------------------------------------------------------------
	 * Returns the name of the Category, given its ID
	 * ------------------------------------------------------------------------
	 * 
	 * @param catID
	 * @return name of the Category
	 */
	private String getCatName(int catID) {
		try {
			String catName = "";
			connection = dbconnection.openConnection();
			Statement st = connection.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT name FROM ex3_categories WHERE catID = '"
							+ catID + "'");
			while (rs.next())
				catName = rs.getString(1);

			st.close();
			rs.close();
			dbconnection.closeConnection();
			return catName;
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
			return Constants.getErrorStr();
		}
	}// end getCatName

	/**
	 * ------------------------------------------------------------------------
	 * Given a new agentID, this method creates a new table (user's model) for
	 * the agent
	 * ------------------------------------------------------------------------
	 * 
	 * @param agentID
	 *            - ID of the agent
	 */
	public void createUserModel(int agentID) {
		try {
			connection = dbconnection.openConnection();
			String query = "CREATE TABLE ex3_agent" + agentID
					+ "_knowledge LIKE " + MySQLConnection.getDBName()
					+ ".ex3_agentX_knowledge; ";
			PreparedStatement s = connection.prepareStatement(query);
			s.executeUpdate();
			s.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
		// CREATE TABLE recipes_new LIKE production.recipes;
		// INSERT recipes_new SELECT * FROM production.recipes;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Deletes the user's model, given its ID
	 * ------------------------------------------------------------------------
	 * 
	 * @param agentID
	 *            - ID of the agent
	 */
	public void dropUserModel(int agentID) {
		try {
			String query = "DROP TABLE IF EXISTS ex3_agent" + agentID
					+ "_knowledge ; ";
			connection = dbconnection.openConnection();
			PreparedStatement s = connection.prepareStatement(query);
			s.execute();
			s.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
	}

	/**
	 * ------------------------------------------------------------------------
	 * Only for debug purpose
	 * ------------------------------------------------------------------------
	 */
	private void cloneTable() {
		try {
			connection = dbconnection.openConnection();
			String query1 = "CREATE TABLE ex3_masterKnowledge LIKE "
					+ MySQLConnection.getDBName() + ".POIS; ";

			PreparedStatement s = connection.prepareStatement(query1);
			s.executeUpdate();

			String query2 = "INSERT ex3_masterKnowledge SELECT * FROM "
					+ MySQLConnection.getDBName() + ".POIS; ";
			s = connection.prepareStatement(query2);
			s.executeUpdate();
			s.close();
			dbconnection.closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			dbconnection.closeConnection();
		}
		// CREATE TABLE recipes_new LIKE production.recipes;
		// INSERT recipes_new SELECT * FROM production.recipes;
	}

	/**
	 * ------------------------------------------------------------------------
	 * Returns all the POIs within a radius
	 * ------------------------------------------------------------------------
	 * 
	 * @param userLocation
	 *            - current user location
	 * @param radius
	 *            - radius to search POIs
	 * @return an arrayList with all the POIs within the radius
	 */
	// public ArrayList<POI> getPOIsNearLocation(Location userLocation, int
	// radius) {
	// Statement stt;
	// ResultSet rs;
	// ArrayList<POI> poisList = new ArrayList<POI>();
	// double lat=0.0, lng = 0.0;
	// Location location = null;
	// POI poi = null;
	// connection = dbconnection.openConnection();
	// try {
	// stt = connection.createStatement();
	// rs =
	// stt.executeQuery("SELECT x.id, x.catID, y.catName, x.subCatID, w.subCatName, x.name, x.lat, x.lng, x.numCheckins, x.price, x.schedule, x.daysOff FROM ex3_masterknowledge x, ex3_categories y, ex3_subcategories w WHERE x.price != '' AND x.catID=y.catID AND x.subCatID=w.subCatID");
	// int i = 3;
	// while (rs.next() && --i > 0) {
	// lat = rs.getDouble(7);
	// lng = rs.getDouble(8);
	// location = new Location(lat, lng);
	//
	// if (location.getGeoPoint().getDistanceMeters(
	// userLocation.getGeoPoint()) <= radius) {
	// poi = createPOI(rs.getInt(1), rs.getInt(2), rs.getString(3)
	// .trim(), rs.getInt(4), rs.getString(5).trim(), rs
	// .getString(6).trim(), rs.getDouble(7),
	// rs.getDouble(8), rs.getInt(9), rs.getString(10)
	// .trim(), rs.getString(11).trim(), rs
	// .getString(12).trim());
	// poisList.add(poi);
	// }
	// }
	// dbconnection.closeConnection();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// dbconnection.closeConnection();
	// }
	// return poisList;
	// }

	/**
	 * ------------------------------------------------------------------------
	 * Used to provide an example that exercises the functionality of the class.
	 * ------------------------------------------------------------------------
	 * 
	 * @param args
	 */
	public static void main(String args[]) {
		DBConnection s = new DBConnection();
		// System.out.println("Getting subCatName:" + s.getSubCatName(133));
		// // s.dropUserModel(2);
		//
		// ArrayList<String> subCate = s.getSubCategoriesNameByGoal("Coffee");
		// for (String sub : subCate) {
		// System.err.println(sub);
		// }

		POI poi = s.getPOI(2114795);
		System.err.println("O pesquisado\n" + poi.to_String() + "\n\n");

		HashMap<Integer, POI> pois = s.getPOIsNearLocation(poi.getLocation(),
				200, new Goal().getDinner());
		for (POI p : pois.values()) {
			System.err.println(p.to_String());
		}

	}

}
// END WriteReadClientData

